// 导入 sequelize执行原生sql
import { sequelize } from "@/data_base/connect/index";
import { response } from "@/utils/response";

/**
 * 执行原生SQL查询
 * @param {string} sql - 要执行的SQL语句
 * @param {Object} [replacements={}] - SQL参数替换对象
 * @returns {Promise<Array>} - 返回查询结果数组
 */
const query = async (sql, replacements = {}) => {
    // console.log("query sql:", sql);
    const res = await sequelize.query(sql, {
        replacements,
    });
    return res;
};

/**
 * 获取按出现次数排序的前几名标签
 * @param {number} limit - 要获取的标签数量
 * @returns {Promise<Object>} - 返回响应对象，包含状态码、数据和消息
 */
const getTopTagsByOccurrenceCount = async limit => {
	// 验证 limit 参数
	if (typeof limit !== "number" || limit <= 0) {
		return response(400, null, "limit 参数必须为正整数");
	}
	let sql = `
    SELECT 
        wt.id AS tag_id,
        wt.name AS name,
        COUNT(*) AS occurrence_count
    FROM 
        webs,
        json_each(webs.tags) AS jt
    JOIN 
        web_tags wt ON jt.value = wt.id
    GROUP BY 
        wt.id, wt.name
    ORDER BY 
        occurrence_count DESC
    LIMIT :limit;  -- 取前 10 名（可以改成任意数字）
    `;
	try {
		const topTags = await query(sql, {
			limit,
		});
		return response(200, topTags[0], "获取常用标签成功！");
	} catch (error) {
		return response(500, null, "获取常用标签失败！");
	}
};

/**
 * 根据标签ID删除标签及相关数据
 * @param {number} tag_id - 要删除的标签ID
 * @returns {Promise<Object>} - 返回响应对象，包含状态码和消息
 */
const delWebTagById = async tag_id => {
    try {
        // 使用 Sequelize 开启事务
        const transaction = await sequelize.transaction();
        try {
            // 更新 webs 表的 SQL 语句
            const updateSql = `
                UPDATE webs
                SET tags = (
                    SELECT json_group_array(value)
                    FROM (
                        SELECT value 
                        FROM json_each(webs.tags)
                        WHERE value != :tag_id
                    )
                )
                WHERE EXISTS (
                    SELECT 1 FROM json_each(webs.tags) 
                    WHERE value = :tag_id
                );
            `;
            // 执行更新操作
            await query(updateSql, { tag_id }, { transaction });

            // 删除 web_tags 表记录的 SQL 语句
            const deleteSql = `
                DELETE FROM web_tags
                WHERE id = :tag_id;
            `;
            // 执行删除操作
            await query(deleteSql, { tag_id }, { transaction });

            // 提交事务
            await transaction.commit();
            return response(200, null, "删除标签成功！");
        } catch (error) {
            // 出现错误，回滚事务
            await transaction.rollback();
            throw error;
        }
    } catch (error) {
        return response(500, null, "删除标签失败！");
    }
};

export { getTopTagsByOccurrenceCount, delWebTagById };